﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class DA_Desk_SQL
    {
        public static string InsertPDA()
        {
            return @"
            INSERT INTO i_DADesk_PDA
           ([DAType]
           ,[Company]
           ,[AgentCode]
           ,[AgentName]
           ,[VesselCode]
           ,[VesselName]
            ,[VoyageNo]
           ,[PortCode]
           ,[TransactionDate]
           ,[Amount]
           ,[Currency]
           ,[DADeskRef]
           ,[ETA]
           ,[AccountCode]
           ,[CreatedDate]
           ,[ModifiedDate]
           ,[Status]
            ,[Remark]
            ,[FileName]
            )
     VALUES
           (@DAType
           ,@Company
           ,@AgentCode
           ,@AgentName
           ,@VesselCode
           ,@VesselName
            ,@VoyageNo
           ,@PortCode
           ,@TransactionDate
           ,@Amount
           ,@Currency
           ,@DADeskRef
           ,@ETA
           ,@AccountCode
           ,@CreatedDate
           ,@ModifiedDate
           ,@Status
            ,@Remark
            ,@FileName)
            ";
        }

        public static string AssertExistPDA()
        {
            return @"
                SELECT count(*) FROM i_DADesk_PDA  
                WHERE  
           [DAType] = @DAType  AND
           [Company] = @Company  AND
           [AgentCode] = @AgentCode  AND
           [AgentName] = @AgentName  AND
           [VesselCode] = @VesselCode  AND
           [VesselName] = @VesselName  AND
           [VoyageNo] = @VoyageNo  AND            
           [PortCode] = @PortCode  AND
           [TransactionDate] = @TransactionDate  AND
           [Amount] = @Amount  AND
           [Currency] = @Currency  AND
           [DADeskRef] = @DADeskRef  AND
           [ETA] = @ETA  AND
           [AccountCode] = @AccountCode  AND
           [Status] = '0'
            ";
        }

        public static string GetAllVessel()
        {
            return @"select * from i_DaDeskVessel";
        }

        public static string GetAllAgent()
        {
            return @"select * from i_DaDeskAgent";
        }

        public static string GetAllBank()
        {
            return @"select * from i_DaDeskBank";
        }

        public static string GetAllCompany()
        {
            return @"select * from i_DaDeskCompany";
        }

        public static string GetAllAccount()
        {
            return @"select * from i_DaDeskAccount";
        }

        public static string GetAllPort()
        {
            return @"select * from i_DaDeskPort";
        }

        public static string GetOutStandingFDA()
        {
            return "select * from i_DADesk_PDA where Status = '0' and DADeskRef = @DADeskRef and DAType in ('FDA', 'VI', 'SDA')";
        }

        public static string GetOutStandingDADeskRefFDA()
        {
            return "select distinct DADeskRef from i_DADesk_PDA where Status = '0' and DAType in ('FDA', 'VI', 'SDA')";
        }

        public static string GetOutStandingPDA()
        {
            return "select * from i_DADesk_PDA where Status = '0' and DADeskRef = @DADeskRef and DAType in ('PDA')";
        }

        public static string GetOutStandingDADeskRefPDA()
        {
            return "select distinct DADeskRef from i_DADesk_PDA where Status = '0' and DAType in ('PDA')";
        }

        public static string UpdateFlagStatus()
        {
            return "update i_DADesk_PDA set Status=@Status, ModifiedDate=@ModifiedDate, Remark=@Remark, batch_id=@batch_id where pda_id=@pda_id";
        }

        public static string FindVesselByName()
        {
            return "select * from i_DaDeskVessel where Vessel_Name =@Vessel_Name";
        }

        public static string InsertVessel()
        {
            return @"
            INSERT INTO i_DaDeskVessel
           ([Vessel_Name]
           ,[Vessel_Code]
           ,[AgrVessel_Code]
           ,[AgrVessel_Name]
           ,[AgrVessel_CostCentre])
     VALUES
           (@Vessel_Name
           ,@Vessel_Code
           ,@AgrVessel_Code
           ,@AgrVessel_Name
           ,@AgrVessel_CostCentre)";
        }

        public static string UpdateVessel()
        {
            return @"
            update i_DaDeskVessel set 
            [AgrVessel_Code] = @AgrVessel_Code
           ,[AgrVessel_Name] = @AgrVessel_Name
           ,[AgrVessel_CostCentre] = @AgrVessel_CostCentre
            where DaDeskVessel_id=@DaDeskVessel_id";
        }

        public static string UpdateAgent()
        {
            return @"
            update i_DaDeskAgent set 
            [AgrAgent_Code] = @AgrAgent_Code
           ,[AgrAgent_Name] = @AgrAgent_Name
           ,[AgrClient] = @AgrClient
            where DaDeskAgent_id=@DaDeskAgent_id";
        }

        public static string UpdateCompany()
        {
            return @"
            update i_DaDeskCompany set 
            [AgrCompany_Code] = @AgrCompany_Code
           ,[AgrCompany_Name] = @AgrCompany_Name
           ,[AgrClient] = @AgrClient
            where DaDeskCompany_id=@DaDeskCompany_id";
        }

        public static string UpdateAccount()
        {
            return @"
            update i_DaDeskAccount set 
            [AccountCode] = @AccountCode
           ,[AgrAccountCode] = @AgrAccountCode
           ,[modified_date] = GETDATE()
            where DaDeskAccount_id=@DaDeskAccount_id";
        }

        public static string UpdatePort()
        {
            return @"
            update i_DaDeskPort set 
            [PortCode] = @PortCode
           ,[AgrPortCode] = @AgrPortCode
           ,[modified_date] = GETDATE()
            where DaDeskPort_id=@DaDeskPort_id";
        }

        public static string UpdateBank()
        {
            return @"
            update i_DaDeskBank set 
           [da_type] = @da_type
           ,[biz_unit] =@biz_unit
           ,[account_code]=@account_code
           ,[bank_code]=@bank_code
            where dadeskbank_id=@dadeskbank_id";
        }

        public static string DeleteBank()
        {
            return @"
            delete from i_DaDeskBank 
            where dadeskbank_id=@dadeskbank_id";
        }

        public static string FindAgentByName()
        {
            return "select * from i_DaDeskAgent where Agent_Name =@Agent_Name";
        }

        public static string InsertAgent()
        {
            return @"
            INSERT INTO i_DaDeskAgent
           ([Agent_Name]
           ,[Agent_Code]
           ,[AgrAgent_Code]
           ,[AgrAgent_Name]
           ,[AgrClient])
     VALUES
           (@Agent_Name
           ,@Agent_Code
           ,@AgrAgent_Code
           ,@AgrAgent_Name
           ,@AgrClient)
            ";
        }

        public static string InsertBank()
        {
            return @"
           INSERT INTO i_DaDeskBank
           ([da_type]
           ,[biz_unit]
           ,[account_code]
           ,[bank_code])
     VALUES
           (@da_type
           ,@biz_unit
           ,@account_code
           ,@bank_code)
            ";
        }

        public static string GetMaxBankID()
        {
            return @"select max(dadeskbank_id) from i_DaDeskBank";
        }

        public static string FindCompanyByName()
        {
            return "select * from i_DaDeskCompany where Company =@Company";
        }

        public static string FindAccountByName()
        {
            return "select * from i_DaDeskAccount where AccountCode =@AccountCode";
        }

        public static string FindPortByName()
        {
            return "select * from i_DaDeskPort where PortCode =@PortCode";
        }

        public static string InsertCompany()
        {
            return @"
            INSERT INTO i_DaDeskCompany
           ([Company]
           ,[AgrCompany_Code]
           ,[AgrCompany_Name]
           ,[AgrClient])
     VALUES
           (@Company
           ,@AgrCompany_Code
           ,@AgrCompany_Name
           ,@AgrClient)
            ";
        }

        public static string InsertAccount()
        {
            return @"
            INSERT INTO i_DaDeskAccount
           ([AccountCode]
           ,[AgrAccountCode]
           ,[insert_date]
           ,[modified_date])
     VALUES
           (@AccountCode
           ,@AgrAccountCode
           ,GETDATE()
           ,GETDATE())
            ";
        }

        public static string InsertPort()
        {
            return @"
            INSERT INTO i_DaDeskPort
           ([PortCode]
           ,[AgrPortCode]
           ,[insert_date]
           ,[modified_date])
     VALUES
           (@PortCode
           ,@AgrPortCode
           ,GETDATE()
           ,GETDATE())
            ";
        }

        public static string UpdateDADeskRemark()
        {
            return @"update i_DADesk_PDA set [Status] = @Status
                      ,[Remark] =@Remark
                      ,[batch_id] = @batch_id 
                        ,[ModifiedDate] = GETDATE()
                    where pda_id = @pda_id";
        }
    }
}
